import sqlite3;
import os.path;
import gtk.gdk;
import cStringIO;
import threading;
import xmp;

OPTION_THUMBNAIL_FORMAT = 'thumbnailFormat';
OPTION_MAX_WIDTH = 'thumbnailWidth';
OPTION_MAX_HEIGHT = 'thumbnailHeight';

DEFAULT_OPTIONS = { 
    OPTION_THUMBNAIL_FORMAT: 'png', 
    OPTION_MAX_WIDTH: 128, 
    OPTION_MAX_HEIGHT: 96 };
    
class DictionaryElement(xmp.Choice):

    def __init__(self,value):
        self.children = [];
        self.parent = None;
        super(DictionaryElement,self).__init__(value);
        
    def addChild(self,child):
        child.parent = self;
        self.children.append(child);
        
class Dictionary(object):
    
    def __init__(self):
        self.roots = [];
        self.elements = {}
        
    def addElement(self,parent,element):
        self.elements[element.value] = element;
        if parent != None:
            parent.addChild(element);
        else:
            self.roots.append(element);
            
    def getElement(self,value):
        if self.elements.has_key(value):
            return self.elements[value];
        return None;
        
class Catalog(object):
    
    lock = threading.RLock()

    def loadOptions(self):
        self.lock.acquire();
        try:
            self.options = {};
            rows = self.query('select name,value from options').fetchall();
            for name,value in rows:
                self.options[name] = value;
        finally:
            self.lock.release();

    def createTables(self):
        self.query('create table options ( name primary key, value text )');
        self.query('create table thumbs ( file text primary key, thumb blob )');
        self.query('create table files ( file text primary key, dir text, name text, mimetype text )');
        self.query('create table tags ( file text, tag text, path text, value text )');
        self.query('create table dictionaries ( path text, value text, parent text, primary key ( path, value) )');
        self.query('create table labels ( path text, lang text, value text, label text, primary key ( path, lang, value ) )');
        self.query('create table descriptions ( path text, lang text, value text, description text, primary key ( path, lang, value ) )');
        self.query('create index tags_ix1 on tags ( file, tag )');
        self.query('create index tags_ix2 on tags ( value )');
        self.query('create trigger delete_thumbs_tags delete on files' 
                   '  begin'
                   '    delete from thumbs where file = old.file;'
                   '    delete from tags where file = old.file;'
                   '  end;');
        self.query('create trigger delete_dictionaries delete on dictionaries' 
                   '  begin'
                   '    delete from labels where path = old.path;'
                   '    delete from descriptions where path = old.path;'
                   '  end;');
        self.options = {};
        for name,value in DEFAULT_OPTIONS.iteritems():
            self.setOption(name,value);
        for property in xmp.properties.itervalues():
            property.walkTypes(self.addChoices);
            
    def addChoices(self,type):
        for choice in type.choices.itervalues():
            fvalue = type.format(choice.value);
            self.query('insert into dictionaries ( path, value, parent ) values ( ?,?,null )',(type.path,fvalue));
            for lang,label in choice.labels.iteritems():
                self.query('insert into labels ( path, lang, value, label ) values ( ?,?,?,? ) ',(type.path,lang,fvalue,label));
            for lang,description in choice.descriptions.iteritems():
                self.query('insert into descriptions ( path, lang, value, description ) values ( ?,?,?,? ) ',(type.path,lang,fvalue,description));
        
    def query(self,q,params=()):
        return self.cursor.execute(q,params);
    
    def __init__(self,path):
        exists = os.path.exists(path);
        self.path = path;
        # Aggiungo uno '/' finale alla directory per capire se un file e' relativo
        self.dir = os.path.join(os.path.dirname(self.path),'');
        self.conn = sqlite3.connect(path,check_same_thread=False);
        self.cursor = self.conn.cursor();
        self.dictionaries = {};
        if not exists:
            self.createTables();
        self.loadOptions();
        self.dictionaryChangedCallbacks = [];
        self.lock = threading.RLock();
        
    def getThumbnail(self,file):
        self.lock.acquire();
        try:
            res = self.query('select thumb from thumbs where file = ?',(file,)).fetchone();
            if res == None:
                return None;
            if res[0] == None:
                return None;
            loader = gtk.gdk.PixbufLoader();
            try:
                loader.write(res[0]);
            finally:
                loader.close();
            return loader.get_pixbuf();
        finally:
            self.lock.release();
        
    def setThumbnail(self,file,pixbuf):
        self.lock.acquire();
        try:
            buf = cStringIO.StringIO();
            pixbuf.save_to_callback(buf.write,'png');
            self.query('delete from thumbs where file = ?',(file,));
            self.query('insert into thumbs values (?,? )',(file,buffer(buf.getvalue())));
            buf.close();
            self.conn.commit();
        finally:
            self.lock.release();
    
    def getAllFiles(self):
        self.lock.acquire();
        try:
            return self.query('select file from files').fetchall();
        finally:
            self.lock.release();
            
    def getFilesWithoutThumbnail(self):
        self.lock.acquire();
        try:
            return self.query('select file from thumbs where thumb is null').fetchall();
        finally:
            self.lock.release();

    def removeFile(self,file):
        self.lock.acquire();
        try:
            self.query('delete from files where file = ?',(file,));
            self.conn.commit();
            return True;
        finally:
            self.lock.release();
        return False;
    
    def normalizeFile(self,file):
        if os.path.isabs(file) and file.startswith(self.dir):
            return file[len(self.dir):];
        return file;
    
    def addFile(self,file,mimetype):
        self.lock.acquire();
        try:
            self.query('insert into files values (?,?,?,? )',(
                file,
                os.path.dirname(file),
                os.path.basename(file),
                mimetype ));
            self.query('insert into thumbs ( file, thumb ) values ( ?,null )', (file,));
            self.conn.commit();
            return True;
        finally:
            self.lock.release();
        return False;
    
    def resolveFile(self,file):
        if os.path.isabs(file):
            return file;
        else:
            return os.path.join(self.dir,file);
    
    def addTag(self,file,tag,path,type,value):
        if type.isSimple():
            self.query('insert into tags (file,tag,path,value) values (?,?,?,? )',(file,tag,path,type.format(value)));
        elif type.isSequence() or type.isBag():
            i = 0;
            for v in value:
                if path == None:
                    self.addTag(file,tag,str(i),type.elementsType,v);
                else:
                    self.addTag(file,tag,path+'.'+str(i),type.elementsType,v);
        elif type.isAlternative():
            for k,v in value.iteritems():
                if path == None:
                    self.addTag(file,tag,k,type.elementsType,v);
                else:
                    self.addTag(file,tag,path+'.'+k,type.elementsType,v);
        elif type.isStructure():
            for k,v in value.iteritems():
                field = type.getField(k);
                if field != None:
                    if path == None:
                        self.addTag(file,tag,k,field.typedecl,v);
                    else:
                        self.addTag(file,tag,path+'.'+k,field.typedecl,v);
        else:
            return False;
        return True;
    
    def setTag(self,file,tag,path,type,value):
        self.query('delete from tags where file = ? and tag = ? and path like ?',(file,tag,path+'%',));
        self.addTag(file, tag, path, type, value);
        
    def setTagByValue(self,file,tag,path,type,value,oldValue):
        path = self.query('select path from tags where file = ? and tag = ? and path like ? and value = ?',(file,tag,path+'%',type.format(oldValue))).fetchone()[0];
        self.setTag(file, tag, path, type, value);
            
    def addTags(self,file,tags):
        self.lock.acquire();
        try:
            for tag,value in tags.iteritems():
                prop = xmp.getProperty(tag);
                if prop != None:
                    self.addTag(file, tag, None, prop.typedecl, value);
            self.conn.commit();
        finally:
            self.lock.release();
            
    def clearTags(self,file):
        self.lock.acquire();
        try:
            self.query('delete from tags where file = ?',(file,));
            self.conn.commit();
        finally:
            self.lock.release();
        
    def clearTag(self,file,tag):
        self.lock.acquire();
        try:
            self.query('delete from tags where file = ? and tag = ?',(file,tag));
            self.conn.commit();
        finally:
            self.lock.release();
        
    def replaceTags(self,file,tags):
        self.lock.acquire();
        try:
            self.clearTags(file);
            self.addTags(file,tags);
        finally:
            self.lock.release();
        
    def getTags(self,file):
        self.lock.acquire();
        try:
            tags = {};
            rows = self.query('select tag,path,value from tags where file = ?',(file,)).fetchall();
            for tag,path,value in rows:
                prop = xmp.getProperty(tag);
                if prop != None:
                    if tags.has_key(tag):
                        tags[tag] = self.parseValue(tags[tag], prop.typedecl, value, path);
                    else:
                        tags[tag] = self.parseValue(None, prop.typedecl, value, path);
            return tags;
        finally:
            self.lock.release();
            
    def getTag(self,file,tag,path):
        self.lock.acquire();
        try:
            tags = {};
            prop = xmp.getProperty(tag);
            if prop == None:
                return None;
            if path == None or len(path) == 0:
                rows = self.query('select path,value from tags where file = ? and tag = ?',(file,tag,)).fetchall();
            else:
                rows = self.query('select path,value from tags where file = ? and tag = ? and path like ?',(file,tag,path+"%")).fetchall();
            value = None;
            for path,text in rows:
                value = self.parseValue(value, prop.typedecl, text, path);
            return value;
        finally:
            self.lock.release();
            
    def splitPath(self,path):
        p = path.split('.',1);
        if len(p) > 1:
            return p;
        return (p[0],None);
            
    def parseValue(self,oldValue,type,text,path):
        if type.isSequence():
            a,b = self.splitPath(path);
            value = self.parseValue(None,type.elementsType,text,b);
            if oldValue == None:
                return [value];
            else:
                oldValue.insert(int(a),value);
                return oldValue;
        elif type.isBag():
            a,b = self.splitPath(path);
            value = self.parseValue(None,type.elementsType,text,b);
            if oldValue == None:
                return [value];
            else:
                oldValue.append(value);
                return oldValue;
        elif type.isAlternative():
            lang,b = self.splitPath(path);
            value = self.parseValue(None,type.elementsType,text,b);
            if oldValue == None:
                return {lang:value};
            else:
                oldValue[lang] = value;
                return oldValue;
        elif type.isStructure():
            a,b = self.splitPath(path);
            field = type.getField(a);
            if field != None:
                value = self.parseValue(None,field.typedecl,text,b);
                if oldValue == None:
                    return {a:value};
                else:
                    oldValue[a] = value;
                    return oldValue;
        else:
            return type.parse(text);

    def getOption(self,name):
        if self.options.has_key(name):
            return self.options[name];
        return None;
    
    def getIntOption(self,name):
        value = self.getOption(name);
        if value == None:
            return None;
        return int(value);
    
    def setOption(self,name,value):
        self.lock.acquire();
        try:
            self.query('delete from options where name = ?', (name,));
            self.query('insert into options (name,value) values (?,?)',(name,str(value)));
            self.conn.commit();
            self.options[name] = value;
        finally:
            self.lock.release();

    def getTagValues(self,name):
        self.lock.acquire();
        try:
            return self.query('select value,count(distinct file) from tags where tag = ? group by value', (name,)).fetchall();
        finally:
            self.lock.release();
        
    def countFilesWithTag(self,name):
        self.lock.acquire();
        try:
            return self.query('select count(distinct file) from tags where tag = ?', (name,)).fetchone()[0];
        finally:
            self.lock.release();
    
    def countFilesWithTagValue(self,name,value):
        self.lock.acquire();
        try:
            return self.query('select count(distinct file) from tags where tag = ? and value = ?', (name,value)).fetchone()[0];
        finally:
            self.lock.release();
            
    def getFilesWithTagValue(self,name,value):
        self.lock.acquire();
        try:
            return self.query('select distinct file from tags where tag = ? and value = ?', (name,value)).fetchall();
        finally:
            self.lock.release();

    def getFilesWithTag(self,name):
        self.lock.acquire();
        try:
            return self.query('select distinct file from tags where tag = ?', (name,)).fetchall();
        finally:
            self.lock.release();
            
    def getDictionary(self,path):
        self.lock.acquire();
        try:
            if self.dictionaries.has_key(path):
                return self.dictionaries.get(path);
            type = xmp.getType(path);
            rows = self.query('select parent,value from dictionaries where path = ?', (path,)).fetchall();
            dict = Dictionary();
            for parent,value in rows:
                value = type.parse(value);
                element = dict.getElement(value);
                if element == None:
                    element = DictionaryElement(value);
                    if parent != None:
                        parent = type.parse(parent);
                        parentElement = dict.getElement(parent);
                        if parentElement == None:
                            parentElement = DictionaryElement(value);
                            dict.addElement(None,parentElement);
                    dict.addElement(parent,element);
            rows = self.query('select value,lang,label from labels where path = ?', (path,)).fetchall();
            for value,lang,label in rows:
                element = dict.getElement(type.parse(value));
                if element != None:
                    element.setLabel(lang, label);
            rows = self.query('select value,lang,description from descriptions where path = ?', (path,)).fetchall();
            for value,lang,description in rows:
                element = dict.getElement(type.parse(value));
                if element != None:
                    element.setDescription(lang, description);
            self.dictionaries[path] = dict;
            return dict;
        finally:
            self.lock.release();
            
    def saveDictionary(self,dict,tag):
        self.lock.acquire();
        try:
            property = xmp.getProperty(tag);
            self.query('delete from dictionaries where tag = ?',(property.getPrefixedName(),));
            for element in dict.elements.itervalues():
                if element.parent != None:
                    self.query('insert into dictionaries ( tag, value, parent ) values ( ?,?,? )',(property.getPrefixedName(),property.format(element.value),property.format(element.parent.value)));
                else: 
                    self.query('insert into dictionaries ( tag, value, parent ) values ( ?,?,null )',(property.getPrefixedName(),property.format(element.value)));
                for lang,label in element.labels:
                    self.query('insert into labels ( tag, lang, label ) values ( ?,?,? ) ',(property.getPrefixedName(),lang,label));
                for lang,descriptions in element.descriptions:
                    self.query('insert into descriptions ( tag, lang, description ) values ( ?,?,? ) ',(property.getPrefixedName(),lang,description));
        finally:
            self.lock.release();

    def connectDictionaryChanged(self,callback):
        self.dictionaryChangedCallbacks.add(callback);
        
    def fireDictionaryChanged(self,path):
        for callback in self.dictionaryChangedCallbacks:
            callback.call(self,path);
        